package Pet.Management.dao;

import Pet.Management.domain.PetCareOffer;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Repository
public class PetCareOfferDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private final RowMapper<PetCareOffer> rowMapper = new RowMapper<PetCareOffer>() {
        @Override
        public PetCareOffer mapRow(ResultSet rs, int rowNum) throws SQLException {
            PetCareOffer offer = new PetCareOffer();
            offer.setId(rs.getLong("id"));
            offer.setDemandId(rs.getLong("demand_id"));
            offer.setDemandUserId(rs.getLong("demand_user_id"));
            offer.setAcceptorId(rs.getLong("acceptor_id"));
            offer.setAcceptorName(rs.getString("acceptor_name"));
            offer.setPetName(rs.getString("pet_name"));
            offer.setPetType(rs.getString("pet_type"));
            offer.setPetAge(rs.getString("pet_age"));
            offer.setPetGender(rs.getString("pet_gender"));
            offer.setPrice(rs.getBigDecimal("price"));
            offer.setServiceStartTime(rs.getTimestamp("service_start_time").toLocalDateTime());
            offer.setServiceEndTime(rs.getTimestamp("service_end_time").toLocalDateTime());
            offer.setDescription(rs.getString("description"));
            offer.setStatus(rs.getString("status"));
            offer.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
            return offer;
        }
    };

    public List<PetCareOffer> findByDemandUserId(Long userId, int offset, int limit) {
        String sql = "SELECT * FROM pet_care_offers WHERE demand_user_id = ? ORDER BY created_at DESC LIMIT ? OFFSET ?";
        return jdbcTemplate.query(sql, rowMapper, userId, limit, offset);
    }

    public List<PetCareOffer> findByDemandUserIdAndPetName(Long userId, String petName, int offset, int limit) {
        String sql = "SELECT * FROM pet_care_offers WHERE demand_user_id = ? AND pet_name LIKE ? ORDER BY created_at DESC LIMIT ? OFFSET ?";
        return jdbcTemplate.query(sql, rowMapper, userId, "%" + petName + "%", limit, offset);
    }

    public int countByDemandUserId(Long userId) {
        String sql = "SELECT COUNT(*) FROM pet_care_offers WHERE demand_user_id = ?";
        return jdbcTemplate.queryForObject(sql, Integer.class, userId);
    }

    public int countByDemandUserIdAndPetName(Long userId, String petName) {
        String sql = "SELECT COUNT(*) FROM pet_care_offers WHERE demand_user_id = ? AND pet_name LIKE ?";
        return jdbcTemplate.queryForObject(sql, Integer.class, userId, "%" + petName + "%");
    }

    public PetCareOffer findById(Long id) {
        String sql = "SELECT * FROM pet_care_offers WHERE id = ?";
        List<PetCareOffer> offers = jdbcTemplate.query(sql, rowMapper, id);
        return offers.isEmpty() ? null : offers.get(0);
    }

    public void updateStatus(Long id, String status) {
        String sql = "UPDATE pet_care_offers SET status = ? WHERE id = ?";
        jdbcTemplate.update(sql, status, id);
    }

    public void save(PetCareOffer offer) {
        String sql = "INSERT INTO pet_care_offers (demand_id, demand_user_id, acceptor_id, acceptor_name, " +
                "pet_name, pet_type, pet_age, pet_gender, price, service_start_time, service_end_time, " +
                "description, status, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())";
        
        jdbcTemplate.update(sql,
                offer.getDemandId(),
                offer.getDemandUserId(),
                offer.getAcceptorId(),
                offer.getAcceptorName(),
                offer.getPetName(),
                offer.getPetType(),
                offer.getPetAge(),
                offer.getPetGender(),
                offer.getPrice(),
                offer.getServiceStartTime(),
                offer.getServiceEndTime(),
                offer.getDescription(),
                "pending"
        );
    }
} 